#!/usr/bin/env python
# -*- coding: utf-8 -*-


'''
@Author : JoeyGosson
@Time   : 2019/10/08 14:40
@File   : mysql_model.py
'''

import os, sys, traceback, re
sys.path.append(os.path.abspath(os.path.dirname(os.path.dirname(__file__))))
from modules.resource_manager import Resource_manager
from modules.log_model import create_logger
from config.settings import *
resource_manager = Resource_manager(mysql_flag=True)
resource_manager.mysql_connection("scout1")




class Mysql_model(object):
    def __init__(self, name):
        self.log = create_logger(isPrint=PRINT_LOG)
        self.name = name
        self.this_info = self.quoter_name()

    def quoter_name(self):
        """
        引用者信息
        :return: 引用者类名
        """
        trace_info = traceback.extract_stack()
        if trace_info:
            trace_info = re.sub(",", "", str(trace_info[0]).split(" ")[2])
            task_name = trace_info.split("/")[-1]
            if task_name:
                return task_name

    def query(self, sql):
        '''
        :param sql: SQL语句
        :return: None or 查询结果
        '''
        conn = None
        try:
            conn = resource_manager.mysql_connection(self.name)
        except BaseException as e:
            self.log.info("连接数据库失败,引用者:{},提示:{}".format(self.this_info, str(e)))
        if conn:
            cursor = conn.cursor()
            try:
                cursor.execute(sql)
                data = cursor.fetchall()
                return data
            except BaseException as e:
                self.log.info("引用者:{},操作:{},提示:{}".format(self.this_info, sys._getframe().f_code.co_name, e))

            finally:
                try:
                    cursor.close()
                except:
                    pass
                try:
                    resource_manager.mysql_release_connection(conn)
                except:
                    pass

    def find(self, sql):
        '''
        :param sql: SQL语句
        :return: None or 查询结果的第一个元素
        '''
        conn = None
        try:
            conn = resource_manager.mysql_connection(self.name)
        except BaseException as e:
            self.log.info("连接数据库失败,引用者:{},提示:{}".format(self.this_info, str(e)[:125]))
        if conn:
            cursor = conn.cursor()
            try:
                cursor.execute(sql)
                data = conn.fetchall()
                if(len(data) > 0):
                    return data[0]
                return tuple()
            except BaseException as e:
                self.log.info("引用者:{},操作:{},提示:{}".format(self.this_info, sys._getframe().f_code.co_name, e))
            finally:
                try:
                    cursor.close()
                except:
                    pass
                try:
                    resource_manager.mysql_release_connection(conn)
                except:
                    pass

    def execute(self, sql, values=None):
        '''
        :param sql: SQL语句
        :return: None or 查询结果的第一个元素
        '''
        conn = None
        try:
            conn = resource_manager.mysql_connection(self.name)
        except BaseException as e:
            self.log.info("连接数据库失败,引用者:{},提示:{}".format(self.this_info, str(e)[:125]))
        if conn:
            cursor = conn.cursor()
            try:
                if values:
                    result = cursor.execute(sql, values)
                    conn.commit()
                    return result
                else:
                    result = cursor.execute(sql)
                    conn.commit()
                    return result
            except BaseException as e:
                self.log.info("引用者:{},操作:{},提示:{}".format(self.this_info, sys._getframe().f_code.co_name, e))
            finally:
                try:
                    cursor.close()
                except:
                    pass
                try:
                    resource_manager.mysql_release_connection(conn)
                except:
                    pass

    def affairs_handler(self, del_sql, insert_sql):
        '''
        先删后插事务操作
        :param del_sql: 删除语句
        :param insert_sql: 插入语句
        :return: 删除和插入的结果
        '''
        conn = None
        try:
            conn = resource_manager.mysql_connection(self.name)
            with conn.cursor() as cursor:
                del_ret = cursor.execute(del_sql)
                insert_ret = cursor.execute(insert_sql)
                conn.commit()
            return str(del_ret), str(insert_ret)
        except BaseException as e:
            self.log.info("引用者:{},操作:{},提示:{}".format(self.this_info, sys._getframe().f_code.co_name, e))
        finally:
            try:
                if conn:
                    resource_manager.mysql_release_connection(conn)
            except:
                pass

    def update_many(self, dicts, value_list, saves_table):
        '''
        批量去重插入
        :param dicts: 字典
        :param value_list: 列表，元素为字典的值
        :return: None
        '''
        conn = None
        try:
            conn = resource_manager.mysql_connection(self.name)
        except BaseException as e:
            self.log.info("连接数据库失败,引用者:{},提示:{}".format(self.this_info, str(e)))
        if conn:
            cursor = conn.cursor()
            keys = ', '.join(dicts.keys())
            values = ', '.join(['%s'] * len(dicts))
            sql = 'insert into {table}({keys}) values({values}) on duplicate key update'.format(table=saves_table,
                                                                                                keys=keys, values=values)
            update = ','.join([" {key} = values({key})".format(key=key) for key in dicts])
            sql += update
            try:
                if cursor.executemany(sql, value_list * 2):
                    conn.commit()
                    return True
            except Exception as e:
                self.log.info('数据库操作出错,引用者:{},提示:{}'.format(self.this_info, str(e),value_list))
                conn.rollback()
            finally:
                try:
                    cursor.close()

                except:
                    pass
                resource_manager.mysql_release_connection(conn)

    def update_one(self, dicts, value_tuple, saves_table):
        '''
        单条信息更新插入
        :param dicts: 字典
        :param value_list: 需要保存的数据元组
        :return: result
        '''
        conn = None
        try:
            conn = resource_manager.mysql_connection(self.name)
        except BaseException as e:
            self.log.info("连接数据库失败,引用者:{},提示:{}".format(self.this_info, str(e)))
        if conn:
            cursor = conn.cursor()
            keys = ', '.join(dicts.keys())
            values = ', '.join(['%s'] * len(dicts))
            sql = 'insert into {table}({keys}) values({values}) on duplicate key update'.format(table=saves_table,
                                                                                                keys=keys, values=values)
            update = ','.join([" {key} = values({key})".format(key=key) for key in dicts])
            sql += update
            try:
                if cursor.execute(sql, value_tuple):
                    conn.commit()
                    return True
            except Exception as e:
                self.log.info('数据库操作出错，引用者:{},提示:{},操作的数据为:{}'.format(self.this_info, str(e), str(value_tuple)))
                conn.rollback()
            finally:
                try:
                    cursor.close()

                except:
                    pass
                resource_manager.mysql_release_connection(conn)

